﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WEPMlibrary_Model;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace WEPMconnection_DAL
{
   public  class FileDal
    {
       Conntion Con = new Conntion();
       /// <summary>
       /// 上传文件
       /// </summary>
       /// <param name="files"></param>
       /// <returns></returns>
       public int addFile(File files)
       {
           int resault = 0;
           using (SqlConnection conn=Con.Con())
           {
               try
               {
                   conn.Open();
                   StringBuilder str = new StringBuilder();
                   str.AppendLine("insert into [File](FileTyep,ProjectID,Uid,filename,fileDate,fileState)");
                   str.AppendLine(" values('" + files.FileTyep + "'," + files.ProjectID + "," + files.Uid + ",'" + files.filename + "','" + files.fileDate + "','"+files.fileState+"')");
                   SqlCommand command = new SqlCommand(str.ToString(), conn);
                   resault = command.ExecuteNonQuery();
               }
               catch (Exception)
               {
                   
                   throw;
               }
               finally
               {
                   conn.Close();
               }
               
           }
           return resault;
       }
       /// <summary>
       /// 数据库中的记录数               
       /// </summary>
       /// <returns></returns>
       public int getCount(string duty)
       {
           int resault = 0;
           using (SqlConnection conn = Con.Con())
           {
               try
               {
                   conn.Open();
                   StringBuilder str = new StringBuilder();
                   str.AppendLine("select COUNT(*) from [File] where fileTyep='" + duty + "'");
                   SqlCommand command = new SqlCommand(str.ToString(), conn);
                   resault =Convert.ToInt32(command.ExecuteScalar());
               }
               catch (Exception)
               {

                   throw;
               }
               finally
               {
                   conn.Close();
               }

           }
           return resault;
       }
       /// <summary>
       /// 分页显示文件
       /// </summary>
       /// <returns></returns>
       public List<File> showFile(int index, int size, string duty)
       {
           var pageIndex=(index-1)*size+1;
           var pageSize=index*size;
           SqlConnection conn = Con.Con();
            List<File> list = new List<File>();
           try 
	        {
                conn.Open();
               StringBuilder str = new StringBuilder();
               str.AppendLine("select * from (");
               str.AppendLine("select ROW_NUMBER() over(order by [File].id ) as fid, [File].Id,[File].Uid,[File].filename,[File].[FileTyep] ,[File].fileState,Project.ProjectName,UserInfor.Name,UserInfor.Username, [File].fileDate ");
               str.AppendLine(" from [File] left join Project on [File].ProjectID=Project.Id");
               str.AppendLine("left join UserInfor on [File].Uid=UserInfor.Id   where [File].fileTyep='"+duty+"' "); 
               str.AppendLine(" ) [file]");
               str.AppendLine("where fid between "+pageIndex+" and "+pageSize+"");
               SqlDataAdapter adapter = new SqlDataAdapter(str.ToString(), conn);
               DataTable dt = new DataTable();
               adapter.Fill(dt);
               foreach (DataRow dr in dt.Rows)
               {
                   File files = new File();
                   files.Id = Convert.ToInt32(dr["Id"]);
                   files.fid = Convert.ToInt32(dr["fid"]);
                   files.FileTyep = dr["FileTyep"].ToString();
                   files.filename = dr["filename"].ToString();
                   files.ProjectName = dr["ProjectName"].ToString();
                   files.Name = dr["Name"].ToString();
                   files.fileDate = Convert.ToDateTime(dr["fileDate"]).ToString("yyyy/MM/dd");
                   files.fileState = dr["fileState"].ToString();
                   list.Add(files);
               }
	        }
	        catch (Exception)
	        {
		        throw;
	        }
            finally
           {
                conn.Close();
           }
           return list;
       }
       /// <summary>
       ///根据name找id
       /// </summary>
       /// <param name="username"></param>
       /// <returns></returns>
       public int   findid(string username)
       {
           int resault = 0;
           using (SqlConnection conn = Con.Con())
           {
               try
               {
                   conn.Open();
                   StringBuilder str = new StringBuilder();
                   str.AppendLine("select Id  from UserInfor where UserName='" + username + "'");
                    SqlDataAdapter adapter = new SqlDataAdapter(str.ToString(), conn);
                   DataTable dt = new DataTable();
                   adapter.Fill(dt);
                   foreach (DataRow dr in dt.Rows)
                   {
                       UserInfor user = new UserInfor();
                       user.Id =Convert.ToInt32(dr["Id"]);
                       resault = user.Id;
                   }
               }
               catch (Exception)
               {

                   throw;
               }
               finally
               {
                   conn.Close();
               }

           }
           return resault;
       }
       /// <summary>
       /// 根据用户名照责任
       /// </summary>
       /// <param name="username"></param>
       /// <returns></returns>
        public string  duty(int  uid)
       {
           string  resault = "";
           using (SqlConnection conn = Con.Con())
           {
               try
               {
                   conn.Open();
                   StringBuilder str = new StringBuilder();
                   str.AppendLine("select duty  from UserInfor where id='" + uid + "'");
                    SqlDataAdapter adapter = new SqlDataAdapter(str.ToString(), conn);
                   DataTable dt = new DataTable();
                   adapter.Fill(dt);
                   foreach (DataRow dr in dt.Rows)
                   {
                       UserInfor user = new UserInfor();
                       user.duty = dr["duty"].ToString();
                       resault = user.duty;
                   }
               }
               catch (Exception)
               {

                   throw;
               }
               finally
               {
                   conn.Close();
               }

           }
           return resault;
       }
    }
}
